const express = require('express');
const router = express.Router();
const mysql = require('mysql');
const bcrypt = require('bcrypt');
const constant = require('../data/constant');
const auth = require('../middleware/auth');
const tools = require('../data/tools');

// 提交评分评论接口
router.post('/commit_comment',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "INSERT INTO comment(file_id,uid,username,content) VALUES(?,?,?,?);"+
            "SELECT score,raters FROM files where file_id = ?;"
            let  sqlParams = [
                req.body.file_id,
                req.body.uid,
                req.body.username,
                req.body.commentContent,
                req.body.file_id
            ];
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    // console.log(res_data);
                    // console.log(req.body.rateValue);
                    if(res_data[0].affectedRows == 1){
                        // 计算出新分数
                        let new_score = (res_data[1][0].score*res_data[1][0].raters + req.body.rateValue)/(res_data[1][0].raters+1);
                        let new_raters = res_data[1][0].raters + 1;
                        // 构造语句并执行
                        let  sql = "UPDATE files SET score = ?,raters = ? where file_id = ?;"
                        let  sqlParams = [
                            new_score,
                            new_raters,
                            req.body.file_id,
                        ];
                        connection.query(sql,sqlParams,async (err,data) => {
                            if(err){
                                console.log(err);
                                res.statusCode = 500;
                                res.send({
                                    'res':{
                                        'ok': 0,
                                        'msg':'数据库连接失败'
                                    }
                                });
                            }else{
                                let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                                res_data = JSON.parse(res_data);//把results字符串转为json对象
                                // console.log("更新评分："+res_data);
                                if(res_data.affectedRows == 1){
                                    res.send({
                                        res:{
                                            ok:1,
                                            msg:"评价成功！"
                                        }
                                    });
                                }else{
                                    res.send({
                                        res:{
                                            ok:0,
                                            msg:"出错了，请联系管理员！"
                                        }
                                    })
                                } 
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"出错了，请联系管理员！"
                            }
                        });
                    } 
                }
                connection.release();
            });
        }
    });
});

// 资源版本信息接口
router.post('/version',auth, (req, res) => {
    var Pool = mysql.createPool({
        'host':constant.DB_HOST_NAME,
        'user':constant.DB_USER,
        'password':constant.DB_PWD,
        'database':constant.DB_NAME,
        'multipleStatements': true // 支持执行多条 sql 语句
    });
    Pool.getConnection( (err,connection) => {
        if(err){
            console.log(err);
            res.send({
                'res':{
                    'ok': 0,
                    'msg':'数据库连接失败'
                }
            });
        }else{
            // 构造sql语句
            let  sql = "SELECT * FROM history_version history WHERE file_id = ? order by time desc;"+
            "SELECT username FROM user where uid = "+
            "(SELECT distinct uploader FROM history_version where file_id = ?);"
            let  sqlParams = [
                req.body.file_id,
                req.body.file_id
            ];
            console.log(req.body,sql);
            connection.query(sql,sqlParams,async (err,data) => {
                if(err){
                    console.log(err);
                    res.statusCode = 500;
                    res.send({
                        'res':{
                            'ok': 0,
                            'msg':'数据库连接失败'
                        }
                    });
                }else{
                    let res_data = JSON.stringify(data);//把data对象转为字符串，去掉RowDataPacket
                    res_data = JSON.parse(res_data);//把results字符串转为json对象
                    console.log(res_data);
                    if(res_data.length >= 1){
                        // 处理数据
                        let result = []
                        for(let i in res_data[0]){
                            let temp = {
                                version_id:res_data[0][i].version,
                                update_reason:res_data[0][i].update_log,
                                uploader:res_data[1][0].username,
                                time:tools.convert_time(res_data[0][i].time)
                            }
                            result.push(temp);
                        }
                        res.send({
                            res:{
                                ok:1,
                                data:result
                            }
                        });
                    }else{
                        res.send({
                            res:{
                                ok:0,
                                msg:"无历史版本信息"
                            }
                        })
                    } 
                }
                connection.release();
            });
        }
    });
});
module.exports = router;